package com.budwk.app.sqfw.controllers.admin;

import cn.dev33.satoken.annotation.SaCheckLogin;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.math.MathUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.StrUtil;
import com.budwk.app.sqfw.models.Sqfw_formSummary_new;
import com.budwk.app.sqfw.services.SqfwFormsummaryNewService;
import com.budwk.app.sqfw.vo.AreaAddressVo;
import com.budwk.app.util.poiUtils.nutzBeanUtils.NutzIocUtils;
import com.budwk.starter.common.openapi.annotation.ApiDefinition;
import com.budwk.starter.common.openapi.annotation.ApiOperation;
import com.budwk.starter.common.result.Result;
import lombok.extern.slf4j.Slf4j;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.impl.sql.NutSql;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.util.cri.SqlExpressionGroup;
import org.nutz.ioc.loader.annotation.Inject;
import org.nutz.ioc.loader.annotation.IocBean;
import org.nutz.lang.util.NutMap;
import org.nutz.mvc.annotation.At;
import org.nutz.mvc.annotation.Ok;
import org.nutz.mvc.annotation.POST;
import org.nutz.mvc.annotation.Param;

import java.math.BigDecimal;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

@IocBean
@At("/platform/sqfw/largeScreenV2/")
@ApiDefinition(tag = "大屏")
@Slf4j
public class LargeScreenV2Controller {
    @Inject
    private SqfwFormsummaryNewService sqfwFormsummaryNewService;
    @At
    @Ok("json")
    @POST
    @ApiOperation(name = "基本情况分析")
    public Result<?> basicSituation(@Param("..") AreaAddressVo areaAddressVo) {
        String startDate = DateUtil.offsetDay(new Date(),-30).toDateStr();
        String endDate= DateUtil.formatDate(new Date());
        SqlExpressionGroup areaSql = getAreaSql( areaAddressVo);
        Dao dao =  NutzIocUtils.getBean(Dao.class);
        Sql basicSituation = Sqls.create("select t1.fill_date,t1.num,sum(t1.num) over(ORDER BY fill_date) total from ("+
                " select" +
                " to_char(fill_date,'YYYY-MM-DD') fill_date ," +
                " count(*) as num" +
                " from" +
                " shequ.sqfw_form_summary_new sfsn" +
                " $areaSql and " +//$areaSql带有where
                " (issue_status = '1' or param_data->>'whetherThereIsAnyHiddenDanger'='true' or param_data->>'InCrimeAndEvil'='true' or param_data->>'isExistDanger'='true')"+
                " group by" +
                " to_char(fill_date,'YYYY-MM-DD')" +
                " order by fill_date asc) t1 order by t1.fill_date desc  limit 30");
        basicSituation.setCallback(Sqls.callback.maps());
        basicSituation.setVar("areaSql",areaSql);
        basicSituation.params().set("startDate",startDate).set("endDate",endDate);
        dao.execute(basicSituation);
        List<NutMap> basicSituationList= basicSituation.getList(NutMap.class);

        NutMap data = NutMap.NEW().addv("dataX",basicSituationList.stream().map(s->s.get("fill_date")).collect(Collectors.toList()))
                .addv("dataY1",basicSituationList.stream().map(s->s.get("num")).collect(Collectors.toList()))
                .addv("dataY2",basicSituationList.stream().map(s->s.get("total")).collect(Collectors.toList()));
        return Result.data(data);
    }

    @At
    @Ok("json")
    @POST
    @ApiOperation(name = "数据统计")
    public Result<?> collectionAndStatistics(@Param("..") AreaAddressVo areaAddressVo) {
        SqlExpressionGroup areaSql = getAreaSql( areaAddressVo);
        Dao dao =  NutzIocUtils.getBean(Dao.class);
        Sql downloadNow = Sqls.fetchInt("select count(*)  from public.sys_oss so $areaSql and ( file_name  like '%xlsx' or  file_name like '%pdf')" +
                " and to_char(create_time,'YYYY-MM-DD') = to_char(now(),'YYYY-MM-DD') ");
        downloadNow.setVar("areaSql",areaSql);
        dao.execute(downloadNow);
        int day = sqfwFormsummaryNewService.count(Cnd.where(areaSql)
                .and("to_char(fill_date,'YYYY-MM-DD')", "=" , DateUtil.format(new Date(),"yyyy-MM-dd")));
        //用户当月活跃数
        Date beginOfWeek=DateUtil.beginOfWeek(new Date());
        Date endOfWeek=DateUtil.endOfWeek(new Date());
        Sql activeUserCount = Sqls.fetchInt("select count(distinct create_by) from (  select create_by  from shequ.sqfw_form_summary_new sfsn  where $areaSql and to_char(fill_date,'YYYY-MM-DD') between '$beginOfWeek' and  '$endOfWeek') t1  inner join  shequ.sys_user su  ON su.id=t1.create_by ");
        activeUserCount.setVar("areaSql",areaSql)
                .setVar("beginOfWeek", DateUtil.formatDate(beginOfWeek))
                .setVar("endOfWeek", DateUtil.formatDate(endOfWeek));
        dao.execute(activeUserCount);
        //用户当月活跃数
        Sql userCount = Sqls.fetchInt("select count(distinct id)  from shequ.sys_user su where  $areaSql ");
        userCount.setVar("areaSql",areaSql);
        dao.execute(userCount);
        NutMap data = NutMap.NEW().addv("downloadNow",downloadNow.getInt())
                .addv("day",day)
                .addv("userCount",userCount.getInt())
                .addv("week",activeUserCount.getInt());
        return Result.data(data);
    }


    @At
    @Ok("json")
    @POST
    @ApiOperation(name = "隐患统计")
    public Result<?> hiddenTrouble(@Param("..") AreaAddressVo areaAddressVo) {
        SqlExpressionGroup areaSql = getAreaSql( areaAddressVo);
        int totle = sqfwFormsummaryNewService.count(Cnd.where(Cnd.exps(Sqfw_formSummary_new::getIssueStatus,"=","1")
                .or("param_data->>'whetherThereIsAnyHiddenDanger'","=","true")
                .or("param_data->>'InCrimeAndEvil'","=","true")
                .or("param_data->>'isExistDanger'","=","true") ).and(areaSql));
        Dao dao =  NutzIocUtils.getBean(Dao.class);
        Sql hiddenTrouble = Sqls.create("select " +
                " id,fill_date," +
                " param_data->>'placeName' placeName, " +
                " case  " +
                " when param_data->>'type'='九小场所' then param_data->>'placeType'  " +
                " when type = '社会治安' then param_data->>'placeType'  " +
                " when type = '安全排查' then coalesce((param_data->>'placeType')::jsonb->>'key',param_data->>'mainProducts') " +
                " else  param_data->>'type' end as placeType," +
                " case" +
                " when type ='安全排查整治' OR type ='安全排查' THEN  '安全巡查' else type end as type" +
                " from ( " +
                " select t.* from ( " +
                "  select " +
                "   row_number() over (partition by shops_base_info_id order by fill_date desc) as rk ," +
                " id,issue_status,province,city,district,county,community,type,fill_date,shops_base_info_id,param_data " +
                " from " +
                " shequ.sqfw_form_summary_new )t " +
                " where rk = 1 )t2 where " +
                " (t2.issue_status = '1' or param_data->>'whetherThereIsAnyHiddenDanger'='true' or param_data->>'InCrimeAndEvil'='true' or param_data->>'isExistDanger'='true')  and $areaSql order by fill_date desc");

        hiddenTrouble.setCallback(Sqls.callback.maps());
        hiddenTrouble.setVar("areaSql",areaSql);
        dao.execute(hiddenTrouble);
        List<NutMap> basicSituationList = hiddenTrouble.getList(NutMap.class);

      NutMap data = NutMap.NEW().addv("totle",totle)
              .addv("unsolved",basicSituationList.size())
              .addv("resolved",totle-basicSituationList.size())
              .addv("data",basicSituationList);
        return Result.data(data);
    }

    @At
    @Ok("json")
    @POST
    @ApiOperation(name = "采集分类")
    public Result<?> collectionByType(@Param("..") AreaAddressVo areaAddressVo) {
        SqlExpressionGroup areaSql = getAreaSql( areaAddressVo);

        Dao dao =  NutzIocUtils.getBean(Dao.class);
        Sql collectionByType = Sqls.create("select\n" +
                "\tt1.type,\n" +
                "\tcount(1)\n" +
                "from\n" +
                "\t(\n" +
                "\tselect\n" +
                "\tcase\n" +
                "\t\twhen type = 'MEETING_ACTIVITY_VISIT_RECORD' then '会议/活动/来访记录'\n" +
                "\t\twhen type = '安全排查' then '安全巡查'\n" +
                "\t\twhen type = '安全排查整治' then '安全巡查'\n" +
                "\t\telse type\n" +
                "\tend as type\n" +
                "\tfrom\n" +
                "\t\tshequ.sqfw_form_summary_new\n" +
                "\t$areaSql\n" +
                "\t\tand type is not null\n" +
                "\t\t)  t1\n" +
                "group by t1.type\t\t\n" +
                "order by\n" +
                "\tcount(1) desc");

        collectionByType.setCallback(Sqls.callback.maps());
        collectionByType.setVar("areaSql",areaSql);
        dao.execute(collectionByType);
        List<NutMap> basicSituationList = collectionByType.getList(NutMap.class);
        //下辖区划采集情况
        Sql community = Sqls.create("select $areaTag as name,count(1)  from shequ.sqfw_form_summary_new  $areaSql and community is not null   group by $group order by count(1) desc");
        community.setCallback(Sqls.callback.maps());
        community.setVar("areaSql",areaSql);
        if(StrUtil.isNotBlank(areaAddressVo.getCounty()) ){
            community.setVar("areaTag","community");
            community.setVar("group","community");

        }else if(StrUtil.isNotBlank(areaAddressVo.getDistrict())){
            community.setVar("areaTag","county");
            community.setVar("group","county");
        }else {
            community.setVar("areaTag","district");
            community.setVar("group","district");
        }

        dao.execute(community);
        List<NutMap> communityList = community.getList(NutMap.class);
        //网格员采集情况
        Sql gridStaff = Sqls.create("select\t\n" +
                "\t\tcommunity as name,\n" +
                "\t\tcreate_user_name as username,\n" +
                "\t\tcount(1) as num\n" +
                "\tfrom\n" +
                "\t\tshequ.sqfw_form_summary_new\n" +
                "\t$areaSql and\n" +
                "\t\tcreate_by is not null and create_user_name is not null and community <> '直属' \n" +
                "\tgroup by create_by,create_user_name,community\n" +
                "\torder by num desc\n" );
        gridStaff.setCallback(Sqls.callback.maps());
        gridStaff.setVar("areaSql",areaSql);
        dao.execute(gridStaff);
        List<NutMap> gridStaffList = gridStaff.getList(NutMap.class);
        NutMap data = NutMap.NEW().addv("dataX",basicSituationList.stream().map(s->s.get("type")).collect(Collectors.toList()))
                .addv("dataY",basicSituationList.stream().map(s->s.get("count")).collect(Collectors.toList()))
                .addv("communityList",communityList)
                .addv("gridStaffList",gridStaffList);
        return Result.data(data);
    }

    @At
    @Ok("json")
    @POST
    @ApiOperation(name = "人员统计")
    public Result<?> personnStatistics(@Param("..") AreaAddressVo areaAddressVo) {
        SqlExpressionGroup areaSql = getAreaSql( areaAddressVo);
        //人员采集数
        Dao dao =  NutzIocUtils.getBean(Dao.class);
        Sql totle = Sqls.create("select  id_number  from shequ.sqfw_person_info $areaSql group by id_number  ");
        totle.setCallback(Sqls.callback.maps());
        totle.setVar("areaSql",areaSql);
        dao.execute(totle);
        List<NutMap> totleList = totle.getList(NutMap.class);

        //当月入库数
        Sql theSameMonth = Sqls.create("select * from ( " +
                "select  id_number ,min(create_date) create_date  from shequ.sqfw_person_info $areaSql group by id_number  " +
                ") t1 where  to_char(create_date,'yyyyMM') =  to_char(current_date,'yyyyMM') ");
        theSameMonth.setCallback(Sqls.callback.maps());
        theSameMonth.setVar("areaSql",areaSql);
        dao.execute(theSameMonth);
        List<NutMap> theSameMonthList = theSameMonth.getList(NutMap.class);
        //上月入库数
        Sql lastMonth = Sqls.create("select * from ( " +
                " select  id_number ,min(create_date) create_date  from shequ.sqfw_person_info $areaSql group by id_number  " +
                " ) t1 where  to_char(create_date,'yyyyMM') =  to_char((current_date - interval '1 month') ,'yyyyMM')");
        lastMonth.setCallback(Sqls.callback.maps());
        lastMonth.setVar("areaSql",areaSql);
        dao.execute(lastMonth);
        List<NutMap> lastMonthList = lastMonth.getList(NutMap.class);

        //人员分类
        Sql byType = Sqls.create("select  t3.specialmark ,count(*) from (\n" +
                "select\n" +
                "\tDISTINCT t2.name as specialmark,t1.id_number,t1.name\n" +
                "from\n" +
                "\t(\n" +
                "\tselect\n" +
                "\t\tjsonb_array_elements((jsonb_array_elements(special_mark)::jsonb->>'tag')::jsonb)::jsonb->>'key' as specialmark,\n" +
                "\t\tid_number,\n" +
                "\t\tname\n" +
                "\tfrom\n" +
                "\t\tshequ.sqfw_person_info $areaSql ) t1 left join shequ.sys_dict t2\n" +
                "\ton t1.specialmark = t2.code\n" +
                ")t3 group by t3.specialmark order by count desc");
        byType.setCallback(Sqls.callback.maps());
        byType.setVar("areaSql",areaSql);
        dao.execute(byType);
        List<NutMap> byTypeList = byType.getList(NutMap.class);


        NutMap data = NutMap.NEW().addv("totle",totleList.size())
                .addv("increase",theSameMonthList.size())
                .addv("dataX",byTypeList.stream().map(s->s.get("specialmark")).collect(Collectors.toList()))
                .addv("dataY",byTypeList.stream().map(s->s.get("count")).collect(Collectors.toList()));
        return Result.data(data);
    }


    @At
    @Ok("json")
    @POST
    @ApiOperation(name = "商业实体统计")
    public Result<?> commerceStatistics(@Param("..") AreaAddressVo areaAddressVo) {
        SqlExpressionGroup areaSql = getAreaSql( areaAddressVo);
        //商业实体采集数
        Dao dao =  NutzIocUtils.getBean(Dao.class);
        Sql totle = Sqls.fetchInt("select  count(1) from (\n" +
                "select\n" +
                "\tplace_name,\n" +
                "\tprovince,\n" +
                "\tdistrict,\n" +
                "\tcity,\n" +
                "\tcounty ,\n" +
                "\tcommunity,\n" +
                "\taddress,\n" +
                "\tcreate_date,\n" +
                "\trow_number() over (partition by place_name, province, district, city, county , community, address order by create_date  desc) as rk\n" +
                "from\n" +
                "\tshequ.sqfw_shops_base_info $areaSql\n" +
                "\n" +
                ")t1  where rk=1\n ");
        totle.setVar("areaSql",areaSql);
        dao.execute(totle);
        //当月入库数
        Sql theSameMonth = Sqls.fetchInt("select   count(1) from (\n" +
                "select\n" +
                "\tplace_name,\n" +
                "\tprovince,\n" +
                "\tdistrict,\n" +
                "\tcity,\n" +
                "\tcounty ,\n" +
                "\tcommunity,\n" +
                "\taddress,\n" +
                "\tcreate_date,\n" +
                "\trow_number() over (partition by place_name, province, district, city, county , community, address order by create_date  desc) as rk\n" +
                "from\n" +
                "\tshequ.sqfw_shops_base_info  $areaSql\n"+
                ")t1  where rk=1 and to_char(create_date,'yyyyMM') =  to_char(current_date,'yyyyMM')");
        theSameMonth.setVar("areaSql",areaSql);
        dao.execute(theSameMonth);

        //上月入库数
        Sql lastMonth = Sqls.fetchInt("select  count(1)  from (\n" +
                "select\n" +
                "\tplace_name,\n" +
                "\tprovince,\n" +
                "\tdistrict,\n" +
                "\tcity,\n" +
                "\tcounty ,\n" +
                "\tcommunity,\n" +
                "\taddress,\n" +
                "\tcreate_date,\n" +
                "\trow_number() over (partition by place_name, province, district, city, county , community, address order by create_date  desc) as rk\n" +
                "from\n" +
                "\tshequ.sqfw_shops_base_info $areaSql\n" +
                ")t1  where rk=1 and to_char(create_date,'yyyyMM') =  to_char((current_date - interval '1 month'),'yyyyMM')");
        lastMonth.setVar("areaSql",areaSql);
        dao.execute(lastMonth);
        //燃气商铺
        Sql gasShop = Sqls.fetchInt("select\n" +
                "\tcount(distinct shops_base_info_id) \n" +
                "from\n" +
                "\tshequ.sqfw_form_summary_new sfsn\n" +
                "$areaSql and\n" +
                "\tparam_data ->'safeCheckGasDTO'->>'fireAndElectricityUsage' <> '3'");
        gasShop.setVar("areaSql",areaSql);
        dao.execute(gasShop);
        //食品商铺
        Sql foodStore = Sqls.fetchInt("select\n" +
                "\t count(distinct shops_base_info_id) \n" +
                "from\n" +
                "\tshequ.sqfw_form_summary_new sfsn\n" +
                " $areaSql and\n" +
                "\tparam_data ->>'fileOutputType' <>'[\"-1\"]'");
        foodStore.setVar("areaSql",areaSql);
        dao.execute(foodStore);
        //商业实体分类
        Sql byType=new NutSql();
        if(areaAddressVo.getDistrict().equals("观山湖区")){
            byType = Sqls.create("select t1.name,count(1) from (\n" +
                    "select  id,place_name,address ,main_business_scope as name from (\n" +
                    "select\n" +
                    "\tid,\n" +
                    "\tmain_business_scope,\n" +
                    "\tbusiness_param,\n" +
                    "\tplace_name,\n" +
                    "\tprovince,\n" +
                    "\tdistrict,\n" +
                    "\tcity,\n" +
                    "\tcounty ,\n" +
                    "\tcommunity,\n" +
                    "\taddress,\n" +
                    "\tcreate_date,\n" +
                    "\trow_number() over (partition by place_name, province, district, city, county , community, address order by create_date  desc) as rk\n" +
                    "from\n" +
                    "\tshequ.sqfw_shops_base_info $areaSql  and main_business_scope is not null\n" +
                    ")t1  where rk=1  \n" +
                    ") t1 group by t1.name order by count(1) desc\n");
        }else {
            byType = Sqls.create("select t2.name,count(1) from (\n" +
                    "select  id,place_name,address, (jsonb_array_elements( jsonb_array_elements(business_param::jsonb)->'tag'))->>'key'  as code  from (\n" +
                    "select\n" +
                    "\tid,\n" +
                    "\tmain_business_scope,\n" +
                    "\tbusiness_param,\n" +
                    "\tplace_name,\n" +
                    "\tprovince,\n" +
                    "\tdistrict,\n" +
                    "\tcity,\n" +
                    "\tcounty ,\n" +
                    "\tcommunity,\n" +
                    "\taddress,\n" +
                    "\tcreate_date,\n" +
                    "\trow_number() over (partition by place_name, province, district, city, county , community, address order by create_date  desc) as rk\n" +
                    "from\n" +
                    "\tshequ.sqfw_shops_base_info $areaSql  and  business_param is not null and main_business_scope is not null\n" +
                    ")t1  where rk=1  \n" +
                    ") t1 left join shequ.sys_dict t2 on t1.code = t2.code where t2.name is not null group by t2.name order by count(1) desc\n");
        }

        byType.setCallback(Sqls.callback.maps());
        byType.setVar("areaSql",areaSql);
        dao.execute(byType);
        List<NutMap> byTypeList = byType.getList(NutMap.class);


        NutMap data = NutMap.NEW().addv("totle",totle.getInt())
                .addv("theSameMonth",theSameMonth.getInt())
                .addv("lastMonth",lastMonth.getInt())
                .addv("gasShop",gasShop.getInt())
                .addv("foodStore",foodStore.getInt())
                .addv("dataX",byTypeList.stream().map(s->s.get("name")).collect(Collectors.toList()))
                .addv("dataY",byTypeList.stream().map(s->s.get("count")).collect(Collectors.toList()));
        return Result.data(data);
    }


    @At
    @Ok("json")
    @POST
    @ApiOperation(name = "辖区楼栋")
    public Result<?> districtBuildingStatistics(@Param("..") AreaAddressVo areaAddressVo) {
        SqlExpressionGroup areaSql = getAreaSql( areaAddressVo);
        //商业实体采集数
        Dao dao =  NutzIocUtils.getBean(Dao.class);
        Sql totle = Sqls.create("select  building_name from shequ.sqfw_building_info $areaSql group by building_name ");
        totle.setCallback(Sqls.callback.maps());
        totle.setVar("areaSql",areaSql);
        dao.execute(totle);
        List<NutMap> totleList = totle.getList(NutMap.class);

        //当月入库数
        Sql theSameMonth = Sqls.create("select * from ( " +
                "select  building_name ,min(create_time) create_time  from shequ.sqfw_building_info $areaSql group by building_name  " +
                ")t1 where  to_char(create_time,'yyyyMM') =  to_char(current_date,'yyyyMM')");
        theSameMonth.setCallback(Sqls.callback.maps());
        theSameMonth.setVar("areaSql",areaSql);
        dao.execute(theSameMonth);
        List<NutMap> theSameMonthList = theSameMonth.getList(NutMap.class);
        //上月入库数
        Sql lastMonth = Sqls.create("select * from ( " +
                " select  building_name ,min(create_time) create_date  from shequ.sqfw_building_info $areaSql group by building_name  " +
                " )t1 where  to_char(create_date,'yyyyMM') =  to_char((current_date - interval '1 month') ,'yyyyMM')");
        lastMonth.setCallback(Sqls.callback.maps());
        lastMonth.setVar("areaSql",areaSql);
        dao.execute(lastMonth);
        List<NutMap> lastMonthList = lastMonth.getList(NutMap.class);



        NutMap data = NutMap.NEW().addv("totle",totleList.size())
                .addv("increase",theSameMonthList.size()-lastMonthList.size());
        return Result.data(data);
    }

    @At
    @Ok("json")
    @POST
    @ApiOperation(name = "商铺巡查")
    public Result<?> shopInspection(@Param("..") AreaAddressVo areaAddressVo) {
        SqlExpressionGroup areaSql = getAreaSql( areaAddressVo);
        //商铺
        Dao dao =  NutzIocUtils.getBean(Dao.class);
        Sql shopInspection = Sqls.create("select\n" +
                "\tto_char(fill_date ,'YYYY-mm') as dataX,\n" +
                "\tcount(distinct shops_base_info_id) as dataY\n" +
                "from\n" +
                "\tshequ.sqfw_form_summary_new sfsn\n" +
                "$areaSql and\n" +
                "\tshops_base_info_id is not null and type in( '安全排查整治','安全排查')\n" +
                "group by\n" +
                "\tto_char(fill_date ,'YYYY-mm')\n" +
                "order by to_char(fill_date ,'YYYY-mm') desc\n" +
                "limit 12");
        shopInspection.setCallback(Sqls.callback.maps());
        shopInspection.setVar("areaSql",areaSql);
        dao.execute(shopInspection);
        List<NutMap> shopInspectionList = shopInspection.getList(NutMap.class);


        //下辖区划采集情况
        Sql community = Sqls.create("select  t1.dataX,t1.datay,coalesce(t2.quarter1,0) as quarter1,coalesce(t3.quarter2,0) as quarter2,coalesce(t4.quarter3,0) as quarter3,coalesce(t5.quarter4,0) as quarter4   from \n" +
                "(\n" +
                "select  $areaTag as dataX,count(1) as dataY from shequ.sqfw_shops_base_info ssbi where service_type like '%safety_check%' and $areaSql group by\n" +
                "\t$group\n" +
                ") t1 \n" +
                "left join \n" +
                "(\n" +
                "select  $areaTag as dataX,\n" +
                "count(distinct shops_base_info_id) as quarter1 \n" +
                "from\n" +
                "shequ.sqfw_form_summary_new sfsn\n" +
                "where\n" +
                "extract(month\n" +
                "from\n" +
                "fill_date) between 1 and 3\n" +
                "and $areaSql \n" +
                " \tand type in( '安全排查整治','安全排查')\n" +
                "\tand shops_base_info_id is not null\n" +
                "\tand to_char(fill_date,'yyyy') = to_char(now(),'yyyy')\n" +
                "group by\n" +
                "\t$group\n" +
                ") t2 on t1.dataX=t2.dataX\n" +
                "left join \n" +
                "(\n" +
                "select  $areaTag as dataX,\n" +
                "count(distinct shops_base_info_id) as quarter2 \n" +
                "from\n" +
                "shequ.sqfw_form_summary_new sfsn\n" +
                "where\n" +
                "extract(month\n" +
                "from\n" +
                "fill_date) between 4 and 6\n" +
                "and $areaSql \n" +
                " \tand type in( '安全排查整治','安全排查')\n" +
                "\tand shops_base_info_id is not null\n" +
                "\tand to_char(fill_date,'yyyy') = to_char(now(),'yyyy')\n" +
                "group by\n" +
                "\t$group\n" +
                ") t3 on t1.dataX=t3.dataX\n" +
                "left join \n" +
                "(\n" +
                "select  $areaTag as dataX,\n" +
                "count(distinct shops_base_info_id) as quarter3 \n" +
                "from\n" +
                "shequ.sqfw_form_summary_new sfsn\n" +
                "where\n" +
                "extract(month\n" +
                "from\n" +
                "fill_date) between 7 and 9\n" +
                "and $areaSql \n" +
                " \tand type in( '安全排查整治','安全排查')\n" +
                "\tand shops_base_info_id is not null\n" +
                "\tand to_char(fill_date,'yyyy') = to_char(now(),'yyyy')\n" +
                "group by\n" +
                "\t$group\n" +
                ") t4 on t1.dataX=t4.dataX\n" +
                "left join\n" +
                "(\n" +
                "select  $areaTag as dataX,\n" +
                "count(distinct shops_base_info_id) as quarter4\n" +
                "from\n" +
                "shequ.sqfw_form_summary_new sfsn\n" +
                "where\n" +
                "extract(month\n" +
                "from\n" +
                "fill_date) between 10 and 12\n" +
                "and $areaSql \n" +
                " \tand type in( '安全排查整治','安全排查')\n" +
                "\tand shops_base_info_id is not null\n" +
                "\tand to_char(fill_date,'yyyy') = to_char(now(),'yyyy')\n" +
                "group by\n" +
                "\t$group\n" +
                ") t5 on t1.dataX=t5.dataX");
        community.setCallback(Sqls.callback.maps());
        areaSql.setTop(false);
        community.setVar("areaSql",areaSql);
        if(StrUtil.isNotBlank(areaAddressVo.getCounty()) ){
            community.setVar("areaTag","community");
            community.setVar("group","community");

        }else if(StrUtil.isNotBlank(areaAddressVo.getDistrict())){
            community.setVar("areaTag","county");
            community.setVar("group","county");
        }else {
            community.setVar("areaTag","district");
            community.setVar("group","district");
        }

        dao.execute(community);
        List<NutMap> communityList = community.getList(NutMap.class);




        NutMap data = NutMap.NEW().addv("dataX",shopInspectionList.stream().map(s->s.get("datax")).collect(Collectors.toList()))
                .addv("dataY",shopInspectionList.stream().map(s->s.get("datay")).collect(Collectors.toList()))
                .addv("communityList",communityList);
        return Result.data(data);
    }



    private SqlExpressionGroup getAreaSql(AreaAddressVo areaAddressVo){
        SqlExpressionGroup areaSql = Cnd.exps(AreaAddressVo::getProvince,"=",areaAddressVo.getProvince());
        if(StrUtil.isNotBlank(areaAddressVo.getCity())){
            areaSql.and(Cnd.exps(AreaAddressVo::getCity,"=",areaAddressVo.getCity()) );
        }
        if(StrUtil.isNotBlank(areaAddressVo.getDistrict())){
            areaSql.and(Cnd.exps(AreaAddressVo::getDistrict,"=",areaAddressVo.getDistrict()));
        }if(StrUtil.isNotBlank(areaAddressVo.getCounty())){
            areaSql.and(Cnd.exps(AreaAddressVo::getCounty,"=",areaAddressVo.getCounty()));
        }
        if(StrUtil.isNotBlank(areaAddressVo.getCommunity())){
            areaSql.and(Cnd.exps(AreaAddressVo::getCommunity,"=",areaAddressVo.getCommunity()));
        }
        return areaSql;
    }


    private SqlExpressionGroup getAreaSql2(AreaAddressVo areaAddressVo){
        SqlExpressionGroup areaSql = Cnd.exps(AreaAddressVo::getProvince,"=",areaAddressVo.getProvince());
        if(StrUtil.isNotBlank(areaAddressVo.getCity())){
            areaSql.and(Cnd.exps(AreaAddressVo::getCity,"=",areaAddressVo.getCity()) );
        }
        if(StrUtil.isNotBlank(areaAddressVo.getDistrict())){
            areaSql.and(Cnd.exps(AreaAddressVo::getDistrict,"=",areaAddressVo.getDistrict()));
        }if(StrUtil.isNotBlank(areaAddressVo.getCounty())){
            areaSql.and(Cnd.exps(AreaAddressVo::getCounty,"=",areaAddressVo.getCounty()));
        }
        if(StrUtil.isNotBlank(areaAddressVo.getCommunity())){
            areaSql.and(Cnd.exps(AreaAddressVo::getCommunity,"=",areaAddressVo.getCommunity()));
        }
        return areaSql;
    }



}
